package dao;

import pojo.entity.Goods;
import utils.DbUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public interface GoodsDao {
    //增加商品
    public static void addGoods(Goods goods) throws SQLException {
        //获取连接
        Connection conn = DbUtil.getConnection();
        //sql
        String sql = "INSERT INTO goods(name, price, count)"
                + "values(" + "?,?,?)";
        //预编译
        PreparedStatement ptmt = conn.prepareStatement(sql); //预编译SQL，减少sql执行

        //传参
        ptmt.setString(1, goods.getName());
        ptmt.setDouble(2, goods.getPrice());
        ptmt.setInt(3, goods.getCount());

        //执行
        ptmt.execute();
    }

    //根据id修改商品信息
    public static void updateGoods(Goods goods) throws SQLException {
        //获取连接
        Connection conn = DbUtil.getConnection();
        //sql, 每行加空格
        String sql = "UPDATE goods" +
                " set name=?, price=?, count=?"+
                " where id=?";
        //预编译
        PreparedStatement ptmt = conn.prepareStatement(sql); //预编译SQL，减少sql执行

        //传参
        ptmt.setString(1, goods.getName());
        ptmt.setDouble(2, goods.getPrice());
        ptmt.setInt(3, goods.getCount());
        ptmt.setInt(4, goods.getId());

        //执行
        ptmt.execute();
    }


    //删除商品
    public static void delGoods(Integer id) throws SQLException {
        //获取连接
        Connection conn = DbUtil.getConnection();
        //sql, 每行加空格
        String sql = "delete from goods where id=?";
        //预编译SQL，减少sql执行
        PreparedStatement ptmt = conn.prepareStatement(sql);

        //传参
        ptmt.setInt(1, id);

        //执行
        ptmt.execute();
    }

    //根据id批量删除商品
    public static void delBatchGoodsById(int[] ids) throws SQLException {
        //获取连接
        Connection conn = DbUtil.getConnection();
        for (int id : ids) {
            //sql, 每行加空格
            String sql = "delete from goods where id=?";
            //预编译SQL，减少sql执行
            PreparedStatement ptmt = conn.prepareStatement(sql);

            //传参
            ptmt.setInt(1, id);
            //执行
            ptmt.execute();
        }
    }

    //根据id查询商品
    public static Goods getGoodsById(Integer id) throws SQLException {
        Goods goods = null;
        //获取连接
        Connection conn = DbUtil.getConnection();
        //sql, 每行加空格
        String sql = "select * from  goods where id=?";
        //预编译SQL，减少sql执行
        PreparedStatement ptmt = conn.prepareStatement(sql);
        //传参
        ptmt.setInt(1, id);
        //执行
        ResultSet rs = ptmt.executeQuery();
        while(rs.next()){
            goods = new Goods();
            goods.setId(rs.getInt("id"));
            goods.setName(rs.getString("name"));
            goods.setPrice(rs.getDouble("price"));
            goods.setCount(rs.getInt("count"));
        }
        return goods;
    }

    //根据商品名称查询商品信息
    public static Goods getGoodsByName(String name) throws SQLException {
        Goods goods = null;
        //获取连接
        Connection conn = DbUtil.getConnection();
        //sql, 每行加空格
        String sql = "select * from  goods where name =?";
        //预编译SQL，减少sql执行
        PreparedStatement ptmt = conn.prepareStatement(sql);
        //传参
        ptmt.setString(1, name);
        //执行
        ResultSet rs = ptmt.executeQuery();
        while(rs.next()){
            goods = new Goods();
            goods.setId(rs.getInt("id"));
            goods.setName(rs.getString("name"));
            goods.setPrice(rs.getDouble("price"));
            goods.setCount(rs.getInt("count"));
        }
        return goods;
    }

    //查询全部商品
    public static List<Goods> query() throws SQLException {
        Connection conn = DbUtil.getConnection();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * from goods order by id ASC , price desc");

        List<Goods> gs = new ArrayList<Goods>();
        Goods g = null;
        while(rs.next()){
            g = new Goods();
            g.setId(rs.getInt("id"));
            g.setName(rs.getString("name"));
            g.setPrice(rs.getDouble("price"));
            g.setCount(rs.getInt("count"));
            gs.add(g);
        }
        return gs;
    }
}
